Table¶

Column Name¶

How to create a column name, see Identifier. You can alter created column name by using the RENAME COLUMN Clause of the ALTER TABLE statement.

The following example shows how to create the manager2 table that has the following two columns: full_name and age.

CREATE TABLE manager2 (full_name VARCHAR(40), age INT );

Setting the Column Initial Value (SHARED, DEFAULT)¶

An attribute in a table can be created with an initial SHARED or DEFAULT value. You can change the value of SHARED and DEFAULT in the ALTER TABLE statement.

• SHARED : Column values are identical in all rows. If a value different from the initial value is INSERTed, the column value is updated to a new one in every row.
• DEFAULT : The initial value set when the DEFAULT attribute was defined is stored even if the column value is not specified when a new row is inserted.

The pseudocolumn allows for the DEFAULT value as follows.

CREATE TABLE colval_tbl
( id INT, name VARCHAR SHARED 'AAA', phone VARCHAR DEFAULT '000-0000');
INSERT INTO colval_tbl(id) VALUES (1),(2);
SELECT * FROM colval_tbl;

           id  name                  phone
=========================================================
            1  'AAA'                 '000-0000'
            2  'AAA'                 '000-0000'

--updating column values on every row
INSERT INTO colval_tbl(id, name) VALUES (3,'BBB');
INSERT INTO colval_tbl(id) VALUES (4),(5);
SELECT * FROM colval_tbl;

           id  name                  phone
=========================================================
            1  'BBB'                 '000-0000'
            2  'BBB'                 '000-0000'
            3  'BBB'                 '000-0000'
            4  'BBB'                 '000-0000'
            5  'BBB'                 '000-0000'

--changing DEFAULT value in the ALTER TABLE statement
ALTER TABLE colval_tbl CHANGE phone DEFAULT '111-1111'
INSERT INTO colval_tbl(id) VALUES (6);
SELECT * FROM colval_tbl;

           id  name                  phone
=========================================================
            1  'BBB'                 '000-0000'
            2  'BBB'                 '000-0000'
            3  'BBB'                 '000-0000'
            4  'BBB'                 '000-0000'
            5  'BBB'                 '000-0000'
            6  'BBB'                 '111-1111'

The DEFAULT value of the pseudocolumn can be specified to one or more columns.

CREATE TABLE t (date1 DATE DEFAULT SYSDATE, date2 DATE DEFAULT SYSDATE);
CREATE TABLE t (date1 DATE DEFAULT SYSDATE,
                ts1   TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

AUTO INCREMENT¶

You can define the AUTO_INCREMENT attribute for the column to automatically give serial numbers to column values. This can be defined only for SMALLINT, INTEGER, BIGINT and NUMERIC(p, 0) types.

DEFAULT, SHARED and AUTO_INCREMENT cannot be defined for the same column. Make sure the value entered directly by the user and the value entered by the auto increment attribute do not conflict with each other.

You can change the initial value of AUTO_INCREMENT by using the ALTER TABLE statement. For details, see AUTO_INCREMENT Clause of ALTER TABLE.

CREATE TABLE table_name (id int AUTO_INCREMENT[(seed, increment)]);

CREATE TABLE table_name (id int AUTO_INCREMENT) AUTO_INCREMENT = seed;

• seed : The initial value from which the number starts. All integers (positive, negative, and zero) are allowed. The default value is 1.
• increment : The increment value of each row. Only positive integers are allowed. The default value is 1.

When you use the CREATE TABLE table_name (id int AUTO_INCREMENT) AUTO_INCREMENT = seed; statement, the constraints are as follows:

• You should define only one column with the AUTO_INCREMENT attribute.
• Don't use (seed, increment) and AUTO_INCREMENT = seed together.

CREATE TABLE auto_tbl(id INT AUTO_INCREMENT, name VARCHAR);
INSERT INTO auto_tbl VALUES(NULL, 'AAA'),(NULL, 'BBB'),(NULL, 'CCC');
INSERT INTO auto_tbl(name) VALUES ('DDD'),('EEE');
SELECT * FROM auto_tbl;

           id  name
===================================
            1  'AAA'
            2  'BBB'
            3  'CCC'
            4  'DDD'
            5  'EEE'

CREATE TABLE tbl (id int AUTO_INCREMENT, val string) AUTO_INCREMENT = 3;
INSERT INTO tbl VALUES (NULL,'cubrid');

SELECT * FROM tbl;

           id  val
===================================
            3  'cubrid'

CREATE TABLE t (id int AUTO_INCREMENT, id2 int AUTO_INCREMENT) AUTO_INCREMENT = 5;
ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to  have exactly one AUTO_INCREMENT column and no seed/increment specification.

CREATE TABLE t (i int AUTO_INCREMENT(100, 2)) AUTO_INCREMENT = 3;
ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to  have exactly one AUTO_INCREMENT column and no seed/increment specification.

NOT NULL Constraint¶

A column for which the NOT NULL constraint has been defined must have a certain value that is not NULL. The NOT NULL constraint can be defined for all columns. An error occurs if you try to insert a NULL value into a column with the NOT NULL constraint by using the INSERT or UPDATE statement.

In the following example, if you input NULL value on the id column, it occurs an error because id column cannot have NULL value.

CREATE TABLE const_tbl1(id INT NOT NULL, INDEX i_index(id ASC), phone VARCHAR);

CREATE TABLE const_tbl2(id INT NOT NULL PRIMARY KEY, phone VARCHAR);
INSERT INTO const_tbl2 VALUES (NULL,'000-0000');

Putting value 'null' into attribute 'id' returned: Attribute "id" cannot be made NULL.

UNIQUE Constraint¶

The UNIQUE constraint enforces a column to have a unique value. An error occurs if a new record that has the same value as the existing one is added by this constraint.

You can place a UNIQUE constraint on either a column or a set of columns. If the UNIQUE constraint is defined for multiple columns, the uniqueness is ensured not for each column, but the combination of multiple columns.

In the following example, the second INSERT statement fails because the value of id column is the same as 1 with the value of id column in the first INSERT statement.

--UNIQUE constraint is defined on a single column only
CREATE TABLE const_tbl5(id INT UNIQUE, phone VARCHAR);
INSERT INTO const_tbl5(id) VALUES (NULL), (NULL);
INSERT INTO const_tbl5 VALUES (1, '000-0000');
SELECT * FROM const_tbl5;

           id  phone
===================================
         NULL  NULL
         NULL  NULL
            1  '000-0000'

INSERT INTO const_tbl5 VALUES (1, '111-1111');

ERROR: Operation would have caused one or more unique constraint violations.

In the following example, if a UNIQUE constraint is defined on several columns, this ensures the uniqueness of the values in all the columns.

-- UNIQUE constraint is defined on several columns
CREATE TABLE const_tbl6(id INT, phone VARCHAR, CONSTRAINT UNIQUE(id, phone));
INSERT INTO const_tbl6 VALUES (1,NULL), (2,NULL), (1,'000-0000'), (1,'111-1111');
SELECT * FROM const_tbl6;

           id  phone
===================================
            1  NULL
            2  NULL
            1  '000-0000'
            1  '111-1111'

PRIMARY KEY Constraint¶

A key in a table is a set of column(s) that uniquely identifies each row. A candidate key is a set of columns that uniquely identifies each row of the table. You can define one of such candidate keys a primary key. That is, the column defined as a primary key is uniquely identified in each row.

By default, the index created by defining the primary key is created in ascending order, and you can define the order by specifying ASC or DESC keyword next to the column.

CREATE TABLE pk_tbl (a INT, b INT, PRIMARY KEY (a, b DESC));

CREATE TABLE const_tbl7(
  id INT NOT NULL,
  phone VARCHAR,
  CONSTRAINT pk_id PRIMARY KEY(id)
);

--CONSTRAINT keyword
CREATE TABLE const_tbl8(
  id INT NOT NULL PRIMARY KEY,
  phone VARCHAR
);

--primary key is defined on multiple columns
CREATE TABLE const_tbl8 (
  host_year    INT NOT NULL,
  event_code   INT NOT NULL,
  athlete_code INT NOT NULL,
  medal        CHAR(1)  NOT NULL,
  score        VARCHAR(20),
  unit         VARCHAR(5),
  PRIMARY KEY(host_year, event_code, athlete_code, medal)
);

FOREIGN KEY Constraint¶

A foreign key is a column or a set of columns that references the primary key in other tables in order to maintain reference relationship. The foreign key and the referenced primary key must have the same data type. Consistency between two tables is maintained by the foreign key referencing the primary key, which is called referential integrity.

[ CONSTRAINT < constraint_name > ]
FOREIGN KEY [ <foreign_key_name> ] ( column_name_comma_list1 )
REFERENCES [ referenced_table_name ] ( column_name_comma_list2 )
[ <referential_triggered_action> ]

<referential_triggered_action> :
ON UPDATE <referential_action>
[ ON DELETE <referential_action> ]

<referential_action> :
CASCADE | RESTRICT | NO ACTION | SET NULL

• constraint_name : Specifies the name of the table to be created.
• foreign_key_name : Specifies a name of the FOREIGN KEY constraint. You can skip the name specification. However, if you specify this value, constraint_name will be ignored, and the specified value will be used.
• column_name_comma_list1 : Specifies the name of the column to be defined as a foreign key after the FOREIGN KEY keyword. The column number of foreign keys defined and primary keys must be same.
• referenced_table_name : Specifies the name of the table to be referenced.
• column_name_comma_list2 : Specifies the name of the referred primary key column after the FOREIGN KEY keyword.
• referential_triggered_action : Specifies the trigger action that responds to a certain operation in order to maintain referential integrity. ON UPDATE or ON DELETE can be specified. Each action can be defined multiple times, and the definition order is not significant.
• ON UPDATE : Defines the action to be performed when attempting to update the primary key referenced by the foreign key. You can use either NO ACTION, RESTRICT, or SET NULL option. The default is RESTRICT.
• ON DELETE : Defines the action to be performed when attempting to delete the primary key referenced by the foreign key. You can use NO ACTION, RESTRICT, CASCADE, or SET NULL option. The default is RESTRICT.
• referential_ action : You can define an option that determines whether to maintain the value of the foreign key when the primary key value is deleted or updated.
• CASCADE : If the primary key is deleted, the foreign key is deleted as well. This option is supported only for the ON DELETE operation.
• RESTRICT : Prevents the value of the primary key from being deleted or updated, and rolls back any transaction that has been attempted.
• SET NULL : When a specific record is being deleted or updated, the column value of the foreign key is updated to NULL.
• NO ACTION : Its behavior is the same as that of the RESTRICT option.

--creating two tables where one is referencing the other
CREATE TABLE a_tbl(
  id INT NOT NULL DEFAULT 0 PRIMARY KEY,
  phone VARCHAR(10)
);

CREATE TABLE b_tbl(
  id INT NOT NULL,
  name VARCHAR(10) NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY(id),
  CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES a_tbl(id)
  ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333');
INSERT INTO b_tbl VALUES(1,'George'),(2,'Laura'),(3,'Max');
SELECT a.id, b.id, a.phone, b.name FROM a_tbl a, b_tbl b WHERE a.id=b.id;

   id           id                   phone                 name
======================================================================
    1            1                   '111-1111'            'George'
    2            2                   '222-2222'            'Laura'
    3            3                   '333-3333'            'Max'

--when deleting primary key value, it cascades foreign key value
DELETE FROM a_tbl WHERE id=3;

1 row affected.

SELECT a.id, b.id, a.phone, b.name FROM a_tbl a, b_tbl b WHERE a.id=b.id;

   id           id                   phone                 name
======================================================================
    1            1                   '111-1111'            'George'
    2            2                   '222-2222'            'Laura'

--when attempting to update primary key value, it restricts the operation
UPDATE  a_tbl SET id = 10 WHERE phone = '111-1111';

In the command from line 1,

ERROR: Update/Delete operations are restricted by the foreign key 'fk_id'.

0 command(s) successfully processed.

KEY or INDEX¶

KEY and INDEX are used interchangeably. They create an index that uses the corresponding column as a key.

CREATE TABLE const_tbl4(id INT, phone VARCHAR, KEY i_key(id DESC, phone ASC));

REUSE_OID¶

You can specify the REUSE_OID option when creating a table, so that OIDs that have been deleted due to the deletion of records (DELETE) can be reused when a new record is inserted (INSERT). Such a table is called an OID reusable or a non-referable table.

OID (Object Identifier) is an object identifier represented by physical location information such as the volume number, page number and slot number. By using such OIDs, CUBRID manages the reference relationships of objects and searches, stores or deletes them. When an OID is used, accessibility is improved because the object in the heap file can be directly accessed without referring to the table. However, the problem of decreased reusability of the storage occurs when there are many DELETE/ INSERT operations because the object's OID is kept to maintain the reference relationship with the object even if it is deleted.

If you specify the REUSE_OID option when creating a table, the OID is also deleted when data in the table is deleted, so that another INSERTed data can use it. OID reusable tables cannot be referred to by other tables, and OID values of the objects in the OID reusable tables cannot be viewed.

--creating table with REUSE_OID option specified
CREATE TABLE reuse_tbl (a INT PRIMARY KEY) REUSE_OID;
INSERT INTO reuse_tbl VALUES (1);
INSERT INTO reuse_tbl VALUES (2);
INSERT INTO reuse_tbl VALUES (3);

--an error occurs when column type is a OID reusable table itself
CREATE TABLE tbl_1 ( a reuse_tbl);

ERROR: The class 'reuse_tbl' is marked as REUSE_OID and is non-referable. Non-referable classes can't be the domain of an attribute and their instances' OIDs cannot be returned.

--an error occurs when a table references a OID reusable table

If you specify REUSE_OID together with the collation of table, it can be placed on before or after COLLATE syntax.

CREATE TABLE t3(a VARCHAR(20)) REUSE_OID COLLATE euckr_bin;
CREATE TABLE t4(a VARCHAR(20)) COLLATE euckr_bin REUSE_OID;

Charset and Collation¶

The charset and collation of the table can be designated in CREATE TABLE statement. Please refer Charset and Collation of Tables for details.

Changes of Table Attributes based on Changes of Column Type¶

• Type Change : If the value of the system parameter alter_table_change_type_strict is set to no, then changing values to other types is allowed, but if it is set to yes then changing is not allowed. The default value of the parameter is no. You can change values to all types allowed by the CAST operator. Changing object types is allowed only by the upper classes (tables) of the objects.
• NOT NULL
  • If the NOT NULL constraint is not specified, it will be removed from a new table even though it is present in the existing table.
  • If the NOT NULL constraint is specified in the column to change, the result varies depending on the configuration of the system parameter, alter_table_change_type_strict.
    • If alter_table_change_type_strict is set to yes, the column values will be checked. If NULL exists, an error will occur, and the change will not be executed.
    • If the alter_table_change_type_strict is set to no, every existing NULL value will be changed to a hard default value of the type to change.
• DEFAULT : If the DEFAULT attribute is not specified in the column to change, it will be removed from a new table even though the attribute is present in the existing table.
• AUTO_INCREMENT : If the AUTO_INCREMENT attribute is not specified in the column to change, it will be removed from a new table even though the attribute is present in the existing table.
• FOREIGN KEY : You cannot change the column with the foreign key constraint that is referred to or refers to.
• Single Column PRIMARY KEY
  • If the PRIMARY KEY constraint is specified in the column to change, a PRIMARY KEY is re-created only in which a PRIMARY KEY constraint exists in the existing column and the type is upgraded.
  • If the PRIMARY KEY constraint is specified in the column to change but doesn't exist in the existing column, a PRIMARY KEY will be created.
  • If a PRIMARY KEY constraint exists but is not specified in the column to change, the PRIMARY KEY will be maintained.
• Multicolumn PRIMARY KEY : If the PRIMARY KEY constraint is specified and the type is upgraded, a PRIMARY KEY will be re-created.
• Single Column UNIQUE KEY
  • If the type is upgraded, a UNIQUE KEY will be re-created.
  • If a UNIQUE KEY exists in the existing column and it is not specified in the column to change, it will be maintained.
  • If a UNIQUE KEY exists in the existing column to change, it will be created.
• Multicolumn UNIQUE KEY : If the column type is changed, an index will be re-created.
• Column with a Non-unique Index : If the column type is changed, an index will be re-created.
• Partition Column: If a table is partitioned by a column, the column cannot be changed. Partitions cannot be added.
• Column with a Class Hierarchy : You can only change the tables that do not have a lower class. You cannot change the lower class that inherits from an upper class. You cannot change the inherited attributes.
• Trigger and View : You must redefine triggers and views directly because they are not changed according to the definition of the column to change.
• Column Sequence : You can change the sequence of columns.
• Name Change : You can change names as long as they do not conflict.

Changes of Values based on Changes of Column Type¶

The alter_table_change_type_strict parameter determines whether the value conversion is allowed according to the type change. If the value is no, it can be changed when you change a column type or add a NOT NULL constraint. The default value is no.

When the value of the parameter, alter_table_change_type_strict is no, it will operate depending on the conditions as follows:

• Overflow occurred while converting numbers or character strings to Numbers: It is determined based on symbol of the result type. If it is negative value, it is specified as a minimum value or positive value, specified as the maximum value and a warning message for records where overflow occurred is recorded in the log. For strings, it will follow the rules stated above after it is converted to DOUBLE type.
• Character strings to convert to shorter ones: The record will be updated to the hard default value of the type that is defined and the warning message will be recorded in a log.
• Conversion failure due to other reasons: The record will be updated to the hard default value of the type that is defined and the warning message will be recorded in a log.

If the value of the alter_table_change_type_strict parameter is yes, an error message will be displayed and the changes will be rolled back.

The ALTER CHANGE statement checks the possibility of type conversion before updating a record but the type conversion of specific values may fail. For example, if the value format is not correct when you convert VARCHAR to DATE, the conversion may fail. In this case, the hard default value of the DATE type will be assigned.

The hard default value is a value that will be used when you add columns with the ALTER TABLE ... ADD  COLUMN statement, add or change by converting types with the ALTER TABLE ... CHANGE/MODIFY statement. The operation will vary depending on the system parameter, add_column_update_hard_default in the ADD COLUMN statement.

Hard Default Value by Type